package com.ztc.ZtcTransport.dao;

import com.aqgj.util.Pager;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;
import com.aqgj.util.BaseDaoImpl;
import com.ztc.ZtcTransport.bean.ZtcTransport;

import java.math.BigInteger;
import java.util.List;
import java.util.Map;


@Repository("ZtcTransportDao")
public class ZtcTransportDao extends BaseDaoImpl<ZtcTransport> {

    public String save(ZtcTransport ztcTransport) {
        this.currentSession().save(ztcTransport);
        return ztcTransport.getId();
    }

    //根据当前用户所在公司ID，获取指定状态下，旗下所有公司信息（分页查询）
    public Pager<ZtcTransport> getAllCompany(String currZid, Integer pageSize, Integer pageNumber,Integer type,String search,Integer status,Integer cType) {
        Pager<ZtcTransport> pager = new Pager<>();
        pager.setCurrentPage(pageNumber);
        pager.setPageSize(pageSize);
        String sql ="";
        String countSql="";
        if(cType==0){
            if (status == 3) {//正常列表
                sql = "select z.* from ztc_transport z where z.status<>1 and z.status<>5 and z.type="+type;
                countSql = "select count(*) from ztc_transport z where z.status<>1 and z.status<>5 and z.type="+type;
            }
            if (status == 1) {//待审核列表
                sql = "select z.* from ztc_transport z where (z.status=1 or z.status=5) and z.type="+type;
                countSql = "select count(*) from ztc_transport z where (z.status=1 or z.status=5) and z.type="+type;
                if(type==3){
                    sql = "select z.* from ztc_transport z where z.parent_id='"+currZid+"' and z.type="+type+" and (z.status=1 or z.status=5)";
                    countSql = "select count(*) from ztc_transport z where z.parent_id='"+currZid+"' and z.type="+type+" and (z.status=1 or z.status=5)";
                }
            }
            if (search != null&& !"".equals(search) && status == 3) {
                sql = "select z.* from ztc_transport z,sys_area a where a.CODE=z.area_code and z.status<>1 and z.status<>5 and z.type="+type+" and (z.full_name like '%" + search + "%' or z.address like '%" + search + "%' or z.sign_tel like '%" + search + "%' or a.FULL_NAME like '%" + search + "%' or z.email like '%" + search + "%')";
                countSql = "select count(*) from ztc_transport z,sys_area a where a.CODE=z.area_code and z.status<>1 and z.status<>5 and z.type="+type+" and (z.full_name like '%" + search + "%' or z.address like '%" + search + "%' or z.sign_tel like '%" + search + "%' or a.FULL_NAME like '%" + search + "%' or z.email like '%" + search + "%')";
            }
            if (search != null&& !"".equals(search) && status == 1) {
                sql = "select z.* from ztc_transport z,sys_area a where a.CODE=z.area_code and (z.status=1 or z.status=5) and z.type="+type+" and (z.full_name like '%" + search + "%' or z.address like '%" + search + "%' or z.sign_tel like '%" + search + "%' or a.FULL_NAME like '%" + search + "%' or z.email like '%" + search + "%')";
                countSql = "select count(*) from ztc_transport z,sys_area a where a.CODE=z.area_code and (z.status=1 or z.status=5) and z.type="+type+" and (z.full_name like '%" + search + "%' or z.address like '%" + search + "%' or z.sign_tel like '%" + search + "%' or a.FULL_NAME like '%" + search + "%' or z.email like '%" + search + "%')";
                if(type==3){
                    sql = "select z.* from ztc_transport z,sys_area a where a.CODE=z.area_code and  z.parent_id='"+currZid+"' and (z.status=1 or z.status=5) and z.type="+type+" and (z.full_name like '%" + search + "%' or z.address like '%" + search + "%' or z.sign_tel like '%" + search + "%' or a.FULL_NAME like '%" + search + "%' or z.email like '%" + search + "%')";
                    countSql = "select count(*) from ztc_transport z,sys_area a where a.CODE=z.area_code and z.parent_id='"+currZid+"' and (z.status=1 or z.status=5) and z.type="+type+" and (z.full_name like '%" + search + "%' or z.address like '%" + search + "%' or z.sign_tel like '%" + search + "%' or a.FULL_NAME like '%" + search + "%' or z.email like '%" + search + "%')";
                }
            }
            sql += " order by z.sign_time desc limit ?,?";
            pager.setContent(this.getHibernateTemplate().getSessionFactory().getCurrentSession().createNativeQuery(sql, ZtcTransport.class).setParameter(1, pager.getStart()).setParameter(2, pageSize).getResultList());
            pager.setRecordTotal(((BigInteger) this.createSQLQuery(countSql).getResultList().get(0)).intValue());
       }else{
            if (status == 3) {//正常列表
                sql = "select z.* from ztc_transport z where z.parent_id=? and z.type=? and z.status<>1 and z.status<>5";
                countSql = "select count(*) from ztc_transport z where z.parent_id=? and z.type=? and z.status<>1 and z.status<>5";
            }
            if (status == 1) {//待审核列表
                sql = "select z.* from ztc_transport z where z.parent_id=? and z.type=? and (z.status=1 or z.status=5)";
                countSql = "select count(*) from ztc_transport z where z.parent_id=? and z.type=? and (z.status=1 or z.status=5)";
            }
            if (search != null&& !"".equals(search) && status == 3) {
                sql = "select z.* from ztc_transport z,sys_area a where a.CODE=z.area_code and z.parent_id=? and z.type=? and z.status<>1 and z.status<>5 and (z.full_name like '%" + search + "%' or z.address like '%" + search + "%' or z.sign_tel like '%" + search + "%' or a.FULL_NAME like '%" + search + "%' or z.email like '%" + search + "%')";
                countSql = "select count(*) from ztc_transport z,sys_area a where a.CODE=z.area_code and z.parent_id=? and z.type=? and z.status<>1 and z.status<>5 and (z.full_name like '%" + search + "%' or z.address like '%" + search + "%' or z.sign_tel like '%" + search + "%' or a.FULL_NAME like '%" + search + "%' or z.email like '%" + search + "%')";
            }
            if (search != null&& !"".equals(search) && status == 1) {
                sql = "select z.* from ztc_transport z,sys_area a where a.CODE=z.area_code and z.parent_id=? and z.type=? and (z.status=1 or z.status=5) and (z.full_name like '%" + search + "%' or z.address like '%" + search + "%' or z.sign_tel like '%" + search + "%' or a.FULL_NAME like '%" + search + "%' or z.email like '%" + search + "%')";
                countSql = "select count(*) from ztc_transport z,sys_area a where  a.CODE=z.area_code and z.parent_id=? and z.type=? and (z.status=1 or z.status=5) and (z.full_name like '%" + search + "%' or z.address like '%" + search + "%' or z.sign_tel like '%" + search + "%' or a.FULL_NAME like '%" + search + "%' or z.email like '%" + search + "%')";
            }
            sql += " order by z.sign_time desc limit ?,?";
            pager.setContent(this.getHibernateTemplate().getSessionFactory().getCurrentSession().createNativeQuery(sql, ZtcTransport.class).setParameter(1, currZid).setParameter(2, type).setParameter(3, pager.getStart()).setParameter(4, pageSize).getResultList());
            pager.setRecordTotal(((BigInteger) this.createSQLQuery(countSql).setParameter(1, currZid).setParameter(2, type).getResultList().get(0)).intValue());
        }
        return pager;
    }

    public String update(ZtcTransport ztcTransport) {
        this.currentSession().merge(ztcTransport);
        return ztcTransport.getId();
    }

    //获取所有省公司和地级公司
    public Pager<ZtcTransport> getAreaCompany(Integer pageSize, Integer pageNumber, String search) {
        Pager<ZtcTransport> pager = new Pager<>();
        pager.setCurrentPage(pageNumber);
        pager.setPageSize(pageSize);
        String sql = "select z.* from ztc_transport z where (z.type=1 or z.type=2) ";
        if(search!=null){
            sql="select distinct z.* from ztc_transport z,sys_area a,sys_user u where z.sign_tel=u.telphone and a.CODE=z.area_code and (z.type=1 or z.type=2) and (z.full_name like '%"+search+"%' or z.address like '%"+search+"%' or z.sign_tel like '%"+search+"%' or a.FULL_NAME like '%"+search+"%' or z.email like '%"+search+"%')";
        }
        sql+=" order by z.type asc,z.sign_time desc";
        String temp= " limit ?,?";
        pager.setContent(this.getHibernateTemplate().getSessionFactory().getCurrentSession().createNativeQuery(sql+temp, ZtcTransport.class).setParameter(1, pager.getStart()).setParameter(2, pageSize).getResultList());
        pager.setRecordTotal(this.getHibernateTemplate().getSessionFactory().getCurrentSession().createNativeQuery(sql, ZtcTransport.class).getResultList().size());
        return pager;
    }

    public ZtcTransport getTransportByClassesPriceForSaleId(String classesPriceForSaleId){
        String sql = "select t.* from ztc_classes_price_for_sale p\n" +
                "    inner join ztc_classes_for_sale f on p.fk_classes_for_sale_id = f.id\n" +
                "    inner join ztc_classes c on f.fk_ztc_classes_id = c.id\n" +
                "    inner join ztc_route r on c.fk_ztc_route_id = r.id\n" +
                "    inner join ztc_transport t on r.fk_ztc_transport_id = t.id\n" +
                "    where\n" +
                "    p.id = ?\n";
        return this.currentSession().createNativeQuery(sql,ZtcTransport.class).setParameter(1,classesPriceForSaleId).getSingleResult();
    }

    //获取当前运营公司下所有正常状态子公司
    public List<ZtcTransport> getChildZtcTransportList(String pid){
        String sql="select * from ztc_transport z where z.parent_id=? and (z.status=3 or z.status=5 or z.status=6) and z.type=4 ";
        return this.currentSession().createNativeQuery(sql,ZtcTransport.class).setParameter(1,pid).getResultList();
    }

    //获取所有营业执照到期且未被冻结的运营公司
    public List<ZtcTransport> getExpireZtcTransport(){
        String sql="select * from ztc_transport z where z.type=3 and (z.status=3 or z.status=5 or z.status=6) and z.end_validate_time<curdate() ";
        return this.currentSession().createNativeQuery(sql,ZtcTransport.class).getResultList();
    }

    //获取快递公司列表根据状态(待审核，全部不含待审核)
    public Pager<Map> getExpressListByType(Integer type, Integer pageSize, Integer pageNumber,String search) {
        Pager<Map> pager=new Pager<>();
        pager.setCurrentPage(pageNumber);
        pager.setPageSize(pageSize);
        String sql="SELECT z.id,z.full_name,z.address,z.sign_tel,z.email,z.`status`,z.sign_time,a.FULL_NAME name " +
                   "FROM ztc_transport z,sys_area a " +
                   "WHERE z.area_code=a.CODE AND z.`type`=5 ";
        if (type==1){
            sql+=" and z.status in (1,5) ";
        }else{
            sql+=" and z.status in (2,3,4,6) ";
        }
        if(search!=null||"".equals(search)){
            sql +="and (z.full_name like '%" + search + "%' or z.address like '%" + search + "%' or z.sign_tel like '%" + search + "%' or a.FULL_NAME like '%" + search + "%' or z.email like '%" + search + "%') ";
        }
        pager.setRecordTotal(this.currentSession().createNativeQuery(sql).getResultList().size());
        sql+="  order by z.sign_time desc limit ?,? ";
        pager.setContent(this.currentSession().createNativeQuery(sql).setParameter(1,pager.getStart()).setParameter(2,pageSize).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList());
        return pager;
    }
}